<?php
/*
 * DB Manager, lowly coupled from the code
 * TODO: Raise exceptions once an error hapens when executing the mysql statements. Input validation and escaping special chars to avoid SQL Injection issues.
 * @author Azucena Casado
 */
define("table", "contenido");
class Mysql {
	const HOST = 'localhost';
	const USER = 'root';
	const PASS = '';
	const DB = 'salima';
        
        //Some predefined SQL queries
	const GET = 'SELECT * FROM %s WHERE %s = "%s"';
	const GET_LAST = 'SELECT %s FROM %s ORDER BY 1 DESC LIMIT 1';
        const GET_ALL = 'SELECT %s FROM %s';
        const GET_WHERE = 'SELECT %s FROM %s WHERE %s="%s"';
        const GET_JOIN = 'SELECT %s FROM %s %s JOIN %s ON %s=%s ORDER BY %s %s';
        const REMOVE = 'DELETE FROM %s WHERE %s=%d';
        const INSERT = 'INSERT INTO %s VALUES(%s)';
        const UPDATE = 'UPDATE %s SET %s WHERE %s';
	private $link;


	function __construct(){
		$this->link = mysql_connect(self::HOST, self::USER, self::PASS)
    		or die('No se pudo conectar: ' . mysql_error());
		mysql_select_db(self::DB) or die('No se pudo seleccionar la base de datos');
	}
        /*
         * Retrieves the unique element that has the primary key $id on $table
         * @param $id int
         * @param $table string
         * @return mixed Array
         */
	public function get($table, $id, $value){
		$result = mysql_query(sprintf(self::GET, $table, $id, $value)) or die('Error trying to fetch: ' . $id . ' from '. $table . ':' . mysql_error());
                $ret = $this->returnData($result);
                if(count($ret) != 1) die ('Should return just one element');
                return $ret[0];
        }
	public function getLast($table, $field='id'){	
		$result = mysql_query(sprintf(self::GET_LAST, $field, strtolower($table))) or die('Error trying to fetch: ' . $id . ' from '. $table . ':' . mysql_error());
                $ret = $this->returnData($result);
                if (count($ret) == 0) {
			return array($field => 0);
		}
		else return $ret[0];
        }

        public function getJoin(array $tables, $fields, array $join, $type = 'LEFT', $sort = '1', $ord = 'DESC') {
		
                $result = mysql_query(sprintf(self::GET_JOIN, $fields, $tables[0], $type, $tables[1], $join[0], $join[1], $sort, $ord)) or die('Error trying to fetch: ' . $fields . ' from '. $tables[0] . ', '. $tables[1] .':' . mysql_error());
                return $this->returnData($result);
        }


        public function getAll($table, $fields, $limit = null, $sort = null, $ord = 'ASC'){	
		$result = mysql_query(sprintf(self::GET_ALL, $fields, $table) . ($sort == null? '': ' ORDER BY '. $sort . ' ' . $ord). ($limit == null? '': ' LIMIT '. $limit)) or die('Error trying to fetch: ' . $fields . ' from '. $table . ':' . mysql_error());
                return $this->returnData($result);
        }

        public function getWhere($table, $fields, array $where, $sort = null, $ord = 'ASC'){	
		$result = mysql_query(sprintf(self::GET_WHERE, $fields, $table, $where[0], $where[1]) . ($sort == null? '': ' ORDER BY '. $sort . ' ' . $ord)) or die('Error trying to fetch: ' . $fields . ' from '. $table . ':' . mysql_error());
                return $this->returnData($result);
        }
        /*
         * Receives a mysql resource and parses it, returning an associative array as result
         * @param $data Mysql Resource array
         * @return mixed Array
         */
        private function returnData($data){
                $ret = array();
                while ($row = mysql_fetch_assoc($data)){
                    $ret[] = $row;
                }
                return $ret;
        }
	public function delete($table, array $id){	
                $value = array_values($id);
		$result = mysql_query(sprintf(self::REMOVE, $table, key($id), $value[0])) or die('Failed trying to remove element: ' . mysql_error());
	}
	public function insert($table, array $data){
                $values = 'null';
                foreach ($data as $field){
                    $values .= ", '". $field . "' ";
                }
		$result = mysql_query(sprintf(self::INSERT, $table, $values)) or die('Failed trying to create a new : '. $table . ":" . mysql_error());
                return mysql_insert_id();
        }
        /*
         * Updates the given table with the given $data for the elements that fullfill the $where condition
         * @param $table string
         * @param $data mixed associative array
         * @param $where string
         * @return void
         */
	public function update($table, array $data, $where){	

                $values = '';
                foreach($data as $key => $field) {
                    $values .= $key . '= "' . mysql_real_escape_string($field) . '", ';  
                }
                $values = substr_replace($values ,'',-2);
                //echo sprintf(self::UPDATE, $table, $values, $where);
		mysql_query(sprintf(self::UPDATE, $table, $values, $where)) or die('Failed trying to update a: '. $table . ":" . mysql_error());
		return true;
	}

	function __destruct(){
		mysql_close($this->link);
	}
}
?>
